import openpyxl
import datetime
import time
import os
import sys
import logging
import ChengBanBuMen
import ReplaceBuMen
import configparser
import GenerateCharts
from hanchlogger import logger
import hanchxl
import docx
from docx.shared import RGBColor
import infoChengBanGongDan
import loadExcelFileByColumn
from openpyxl.utils import get_column_letter

timeCur = datetime.datetime.now()
timeCurStr = '{}-{}-{} {}:{}:{}'.format(timeCur.year, timeCur.month, timeCur.day, timeCur.hour, timeCur.minute, timeCur.second)
dirPath = os.path.dirname(os.path.realpath(sys.executable))
# dirPath ='\\\\172.16.10.175\\共享文件\\数据组\\2023年文件\\杨书记调度会\\生成每日必办'
dirPath ='E:\\日常工作\\数据组日常工作-春节\\生成每日必办\\生成每日必办-2025.7.7 - 副本'
dirPathHuiZong = '\\\\10.117.115.111\\共享文件\\数据组\\月周日报数据'

logFile = dirPath + '\\MustDoEveryDay.log'
logger.get_log(logFile, 'LoggerMustDoEveryDay', logging.DEBUG)

iniFilePathCur = dirPath + '\\MustDoEveryDay.ini'
iniConfigMust = configparser.ConfigParser(allow_no_value=True)
iniConfigMust.read(iniFilePathCur, encoding='utf-8')

timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp()-60*60*24).strftime('%Y-%m-%d')
timeYesterdayStr = timeYesterdayStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeYesterday = datetime.datetime.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterday = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterdayStamp = int(time.mktime(timeYesterday))
logger.debug('昨天（日期）：{}    昨天（时间戳）：{}'.format(timeYesterdayStr, timeYesterdayStamp))

timeTomorrowStr = datetime.datetime.fromtimestamp(timeCur.timestamp()+60*60*24).strftime('%Y-%m-%d')
timeTomorrowStr = timeTomorrowStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeTomorrow = datetime.datetime.strptime(timeTomorrowStr, '%Y-%m-%d %H:%M:%S')
timeTomorrow = time.strptime(timeTomorrowStr, '%Y-%m-%d %H:%M:%S')
timeTomorrowStamp = int(time.mktime(timeTomorrow))
strTomorrow = '{}年{}月{}日'.format(datatimeTomorrow.year, datatimeTomorrow.month, datatimeTomorrow.day)
logger.debug('明天（日期）：{}    明天（时间戳）：{}'.format(timeTomorrowStr, timeTomorrowStamp))

strToday = '{}月{}日'.format(timeCur.month, timeCur.day)


def get_config_value(ini_config, section, section_key, value_default):
    sectionValue = value_default
    if ini_config.has_section(section):
        if ini_config.has_option(section, section_key):
            sectionValue = ini_config.get(section, section_key, )
            if sectionValue is not None and sectionValue != '':
                sectionValue = '{}'.format(sectionValue)
    return sectionValue


# 是否自定义工作目录
customizeWorkFolder = get_config_value(iniConfigMust, 'global', 'isCustomizeWorkFolder', '0')
if customizeWorkFolder != '0':
    workFolder = get_config_value(iniConfigMust, 'global', 'workFolder', dirPath)
    if workFolder != '' and dirPath != workFolder:
        if os.path.exists(workFolder):
            dirPath = workFolder
            logger.debug('是否自定义工作目录为：{}'.format(dirPath))
        else:
            logger.debug('自定义的工作目录不存在，工作目录：{}'.format(dirPath))
    else:
        logger.debug('工作目录未填写或者为空，或者与默认值相同，使用默认值：{}'.format(dirPath))
else:
    logger.debug('不使用自定义的工作目录，使用默认值:{}'.format(dirPath))

logger.debug('是否处理今日派单工单列表')
isProcessReceiving = get_config_value(iniConfigMust, 'global', 'isProcessReceiving', '1')
if isProcessReceiving == '0':
    isProcessReceiving = False
    logger.debug('不处理今日派单工单列表，isProcessUnresolved = False')
else:
    isProcessReceiving = True
    logger.debug('处理今日派单工单列表，isProcessUnresolved = True')

# 是否处理未处理工单列表
isProcessUnresolved = get_config_value(iniConfigMust, 'global', 'isProcessUnresolved', '1')
if isProcessUnresolved == '0':
    isProcessUnresolved = False
    logger.debug('不处理未处理工单列表，isProcessUnresolved = False')
else:
    isProcessUnresolved = True
    logger.debug('处理未处理工单列表，isProcessUnresolved = True')

OrderCountFolder = get_config_value(iniConfigMust, 'global', 'OrderCountFolder', '')
if OrderCountFolder is not None and OrderCountFolder != '':
    dirPathHuiZong = dirPath + OrderCountFolder
logger.debug('汇总文件目录：{}'.format(dirPathHuiZong))

fileChengBan = dirPath + '\\承办部门.xlsx'
fileJieDanCount = dirPath + '\\每日必办-工单总数.xlsx'
fileJieDanCountSave = dirPath + '\\每日必办-工单总数-已匹配承办部门.xlsx'
fileKaoHeCount = dirPath + '\\每日必办-区级有效回访.xlsx'
fileKaoHeCountSave = dirPath + '\\每日必办-区级有效回访-已匹配承办部门.xlsx'
fileShiKaoHeCount = dirPath + '\\每日必办-纳入市级考核范围.xlsx'
fileShiKaoHeCountSave = dirPath + '\\每日必办-纳入市级考核范围-已匹配承办部门.xlsx'
fileJieDanToday = dirPath + '\\每日必办-今日接单量-区.xlsx'
fileJieDanTodaySave = dirPath + '\\每日必办-今日接单量-区-匹配承办部门.xlsx'
# fileJieDanTodayJieDao = dirPath + '\\每日必办-今日接单量.xlsx'
fileHuiFangCount = dirPath + '\\每日必办-回访工单明细.xlsx'
fileHuiFangNo = dirPath + '\\每日必办-回访工单明细-未处理.xlsx'
fileHuiFangChengBan = dirPath + '\\每日必办-回访工单明细-匹配承办部门.xlsx'
fileResultKaoHe = dirPath + '\\每日必办-回访工单明细-处理结果.xlsx'

fileSanLvCount = dirPath + '\\每日必办-首页三率统计表.xlsx'
fileSanLvSave = dirPath + '\\[内部] 附件1 {}月{}日街乡镇接诉情况统计表.xlsx'.format(timeCur.month, timeCur.day)
fileResultSave = dirPath + '\\[内部] 附件2 {}月{}日街道机关科室和社区（村）裸分情况统计表.xlsx'.format(timeCur.month, timeCur.day)
fileJieDanInfoSave = dirPath + '\\[内部] 附件3 {}月{}日新增接单三件以上部门情况统计表.xlsx'.format(timeCur.month, timeCur.day)
fileMustDoSave = dirPath + '\\[内部] 每日必办 {}.{}.docx'.format(timeCur.month, timeCur.day)

fileGongNuanInfo = dirPathHuiZong + '\\2024年供暖工单信息.xlsx'
fileRiHuiZong = dirPathHuiZong + '\\日报汇总数据信息.xlsx'
fileImageTuOneSave = dirPath + '\\[内部] 图1. 七日诉求量情况.png'
fileMustDoMuBan = dirPath + '\\模板-每日必办.docx'
fileSanLvMuBan = dirPath + '\\模板-街乡镇接诉情况统计表.xlsx'
fileResultMuBan = dirPath + '\\模板-本考核期裸分情况统计表.xlsx'
fileResultShiFenInfo = dirPath + '\\模板-工单回访情况.xlsx'
fileJieDanInfoMuBan = dirPath + '\\模板-新增接单三件以上部门情况统计表.xlsx'

fileHuiFangQuanBu = dirPath + '\\全部工单.xlsx'
fileChengBanReplace = dirPath + '\\需替换部门.xlsx'
fileColIndexInfo = dirPath + '\\数据列索引信息.xlsx'

chengBan = ChengBanBuMen.ChengBanDirectory()
chengBan.load_cheng_ban_list(fileChengBan)
listChengBanKeShi = chengBan.get_list_ke_shi_array()
listChengBanSheQu = chengBan.get_list_she_qu_array()

listChengBanKeShiInfo = chengBan.get_list_ke_shi()
listChengBanSheQuInfo = chengBan.get_list_she_qu()

# 打开替换部门字典表
logger.debug('打开替换部门字典表 文件：{}'.format(fileChengBanReplace))
replaceBuMen = ReplaceBuMen.ReplaceDirectory()
replaceBuMen.load_directory(fileChengBanReplace)
listReplaceBuMen = replaceBuMen.get_replace_directory()

# 打开 模板-每日必办 文件
docMuBan = docx.Document(fileMustDoMuBan)


class GongDanCountInFo(loadExcelFileByColumn.GongDanCountInFo):
    def __init__(self, name_=''):
        super(GongDanCountInFo, self).__init__(name_)
        self.shiKaoHeCount = 0
        self.shiKaoHeShiPai = 0
        self.shiKaoHeQuPai = 0
        self.kaoHeCount = 0
        self.kaoHeShiPai = 0
        self.kaoHeQuPai = 0
        self.muBiao = 0
        self.quPaiMing = 0
        self.xiaoZuPaiMing = 0


class jieDanInFo(object):
    def __init__(self, time_d=datetime.datetime.now()):
        self.date = '{}-{}-{}'.format(time_d.year, time_d.month, time_d.day)
        self.jieDanCount = 0
        self.jieDanShiPai = 0
        self.jieDanQuPai = 0
        self.jieDanKeShi = 0
        self.jieDanSheQu = 0
        self.jieDanYesterday = 0


class dateJieDanInFo(object):
    def __init__(self):
        self.date = ''
        self.count = 0
        self.shiPai = 0
        self.quPai = 0
        self.gongNuan = 0
        self.gongNuanLastYear = 0


# 打开 全部工单.xlsx
bookQuanBu = openpyxl.load_workbook(fileHuiFangQuanBu, data_only=True)
sheetQuanBu = bookQuanBu.active
maxRowQuanBu = sheetQuanBu.max_row
maxColQuanBu = sheetQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowQuanBu, maxColQuanBu))

fileJieDanCount = dirPath + '\\每日必办-工单总数.xlsx'
# 打开 每日必办-工单总数.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDanCount, data_only=True)
sheetJieDan = bookJieDan.active
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('每日必办-工单总数.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

listZongShu = loadExcelFileByColumn.GongDanCountInFoList('工单总数')
listZongShu.load_gong_dan_info_by_column(sheetJieDan, sheetQuanBu, listReplaceBuMen, 0)
logger.debug('{} 总量：{} '.format(listZongShu.name, listZongShu.CountAll))

bookJieDan.save(fileJieDanCountSave)
logger.debug('保存  {}'.format(fileJieDanCountSave))

# 打开 每日必办-纳入市级考核范围.xlsx
bookShiKaoHe = openpyxl.load_workbook(fileShiKaoHeCount, data_only=True)
sheetShiKaoHe = bookShiKaoHe.active
maxRowShiKaoHe = sheetShiKaoHe.max_row
maxColShiKaoHe = sheetShiKaoHe.max_column
logger.debug('每日必办-纳入市级考核范围.xlsx -------行数：{}行    列数：{}列'.format(maxRowShiKaoHe, maxColShiKaoHe))

listNaRuShiJi = loadExcelFileByColumn.GongDanCountInFoList('纳入市级考核范围')
listNaRuShiJi.load_gong_dan_info_by_column(sheetShiKaoHe, sheetQuanBu, listReplaceBuMen, 1)
logger.debug('{} 总量：{} '.format(listNaRuShiJi.name, listNaRuShiJi.CountAll))

for buMen in listNaRuShiJi.listGogDanInfo:
    if buMen in listChengBanSheQuInfo:
        listNaRuShiJi.listGogDanInfo[buMen].suoShuXiaQu = listChengBanSheQuInfo[buMen].suoShuPianQu

bookShiKaoHe.save(fileShiKaoHeCountSave)
logger.debug('保存  {}'.format(fileShiKaoHeCountSave))

# 打开 每日必办-区级有效回访.xlsx
bookYouXiaoHuiFang = openpyxl.load_workbook(fileKaoHeCount, data_only=True)
sheetYouXiaoHuiFang = bookYouXiaoHuiFang.active
maxRowYouXiaoHuiFang = sheetYouXiaoHuiFang.max_row
maxColYouXiaoHuiFang = sheetYouXiaoHuiFang.max_column
logger.debug('每日必办-区级有效回访.xlsx -------行数：{}行    列数：{}列'.format(maxRowYouXiaoHuiFang, maxColYouXiaoHuiFang))

listYouXiaoHuiFang = loadExcelFileByColumn.GongDanCountInFoList('纳入市级考核')
listYouXiaoHuiFang.load_gong_dan_info_by_column(sheetYouXiaoHuiFang, sheetQuanBu, listReplaceBuMen, 1)
logger.debug('{} 总量：{} '.format(listYouXiaoHuiFang.name, listYouXiaoHuiFang.CountAll))

for buMen in listYouXiaoHuiFang.listGogDanInfo:
    if buMen in listChengBanSheQuInfo:
        listYouXiaoHuiFang.listGogDanInfo[buMen].suoShuXiaQu = listChengBanSheQuInfo[buMen].suoShuPianQu

bookYouXiaoHuiFang.save(fileKaoHeCountSave)
logger.debug('保存  {}'.format(fileKaoHeCountSave))

# 打开 2024年供暖工单信息.xlsx
bookGongNuanInfo = openpyxl.load_workbook(fileGongNuanInfo, data_only=True)
sheetGongNuanInfo2024 = bookGongNuanInfo['2024年供暖工单']
maxRowGongNuanInfo2024 = sheetGongNuanInfo2024.max_row
maxColGongNuanInfo2024 = sheetGongNuanInfo2024.max_column
logger.debug('{} 2024年供暖工单 -------行数：{}行    列数：{}列'.format(fileGongNuanInfo, maxRowGongNuanInfo2024, maxColGongNuanInfo2024))

sheetGongNuanInfo2023 = bookGongNuanInfo['2022-2023年供暖工单']
maxRowGongNuanInfo2023 = sheetGongNuanInfo2023.max_row
maxColGongNuanInfo2023 = sheetGongNuanInfo2023.max_column
logger.debug('{} 2022-2023年供暖工单 -------行数：{}行    列数：{}列'.format(fileGongNuanInfo, maxRowGongNuanInfo2023, maxColGongNuanInfo2023))

# 打开 日报汇总数据信息.xlsx
bookRiHuiZong = openpyxl.load_workbook(fileRiHuiZong, data_only=True)
sheetCountJieDan = bookRiHuiZong['总体情况']
maxRowCountJieDan = sheetCountJieDan.max_row
maxColCountJieDan = sheetCountJieDan.max_column
logger.debug('{} 总体诉求情况 -------行数：{}行    列数：{}列'.format(fileRiHuiZong, maxRowCountJieDan, maxColCountJieDan))

logger.debug('打开首页三率统计表.xlsx')
# 打开 首页三率统计表.xlsx
bookSanLv = openpyxl.load_workbook(fileSanLvCount, data_only=True)
sheetSanLv = bookSanLv.active
maxRowSanLv = sheetSanLv.max_row
maxColSanLv = sheetSanLv.max_column
logger.debug('首页三率统计表.xlsx -------行数：{}行    列数：{}列'.format(maxRowSanLv, maxColSanLv))

# 打开 模板-街乡镇接诉情况统计表.xlsx
bookSanLvMuBan = openpyxl.load_workbook(fileSanLvMuBan, data_only=True)
sheetSanLvMuBan = bookSanLvMuBan.active
maxRowSanLvMuBan = sheetSanLvMuBan.max_row
maxColSanLvMuBan = sheetSanLvMuBan.max_column
logger.debug('模板-街乡镇接诉情况统计表.xlsx -------行数：{}行    列数：{}列'.format(maxRowSanLvMuBan, maxColSanLvMuBan))

strHuiFangStart = '{}月{}日9时'.format(datatimeYesterday.month, datatimeYesterday.day)
strKaoHeStart = ''
strKaoHeStartDay = ''
strTitle = ''
if timeCur.day < 19:
    if timeCur.month == 1:
        strKaoHeStart = '12月19日0时'
        strKaoHeStartDay = '12月19日'
        strTitle = strTitle + '12月19日-{}月{}日'.format(timeCur.month, timeCur.day)
    else:
        strKaoHeStart = '{}月19日0时'.format(timeCur.month - 1)
        strKaoHeStartDay = '{}月19日'.format(timeCur.month - 1)
        strTitle = strTitle + '{}月19日-{}月{}日'.format(timeCur.month - 1, timeCur.month, timeCur.day)
else:
    strKaoHeStart = '{}月19日0时'.format(timeCur.month)
    strKaoHeStartDay = '{}月19日'.format(timeCur.month)
    strTitle = strTitle + '{}月19日-{}月{}日'.format(timeCur.month, timeCur.month, timeCur.day)
strTitle = strTitle + '街乡镇接诉情况统计表\r\n（截至 14:00 裸分排名）'
sheetSanLvMuBan.cell(2, 1).value = strTitle

countInfo = GongDanCountInFo()
countInfo.jieDanCount = listZongShu.CountAll
countInfo.jieDanShiPai = listZongShu.CountShi
countInfo.jieDanQuPai = listZongShu.CountQu

countInfo.shiKaoHeCount = listNaRuShiJi.CountAll
countInfo.shiKaoHeShiPai = listNaRuShiJi.CountShi
countInfo.shiKaoHeQuPai = listNaRuShiJi.CountQu

countInfo.kaoHeCount = listYouXiaoHuiFang.CountAll
countInfo.kaoHeShiPai = listYouXiaoHuiFang.CountShi
countInfo.kaoHeQuPai = listYouXiaoHuiFang.CountQu

countInfo.CountShuangMan = listYouXiaoHuiFang.CountShuangMan
countInfo.CountShuangManShi = listYouXiaoHuiFang.CountShuangManShi
countInfo.CountShuangManQu = listYouXiaoHuiFang.CountShuangManQu

countInfo.lianxi = listYouXiaoHuiFang.lianXi
countInfo.weiLanXi = listYouXiaoHuiFang.weiLanXi
countInfo.jieJue = listYouXiaoHuiFang.jieJue
countInfo.weiJieJue = listYouXiaoHuiFang.weiJieJue
countInfo.manYi = listYouXiaoHuiFang.manYi
countInfo.jiBenManYi = listYouXiaoHuiFang.jiBenManYi
countInfo.buManYi = listYouXiaoHuiFang.buManYi
countInfo.jianMian = listYouXiaoHuiFang.jianMian
countInfo.weiJianMian = listYouXiaoHuiFang.weiJianMian

# 保存考核工单信息
colTitleListArry = ['时间戳', '日期', '接单总量', '接单区派', '接单市派', '考核总量', '考核市派', '考核区派', '联系', '未联系',
                    '解决', '未解决', '满意', '基本满意', '不满意', '见面', '未见面','联系率', '解决率', '满意率', '见面率',
                    '综合得分', '区排名', '小组排名']
colTitleList = {}
for index in colTitleListArry:
    colTitleList[index] = hanchxl.get_col_index(sheetCountJieDan, index)
    # logger.debug('{}:{}'.format(index, colTitleList[index]))

# 保存接单总量信息
for index in colTitleListArry:
    logger.debug('{}:{}'.format(index, colTitleList[index]))
    if index == '时间戳':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = timeCurStr
    elif index == '日期':
        todayStr = '{}-{}-{}'.format(timeCur.year, timeCur.month, timeCur.day)
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = todayStr
    elif index == '接单总量':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieDanCount
    elif index == '接单市派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieDanShiPai
    elif index == '接单区派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieDanQuPai
    elif index == '考核总量':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.kaoHeCount
    elif index == '考核市派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.kaoHeShiPai
    elif index == '考核区派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.kaoHeQuPai
    elif index == '联系':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lianXi
    elif index == '未联系':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.weiLanXi
    elif index == '解决':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieJue
    elif index == '未解决':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.weiJieJue
    elif index == '满意':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.manYi
    elif index == '基本满意':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jiBenManYi
    elif index == '不满意':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.buManYi
    elif index == '见面':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jianMian
    elif index == '未见面':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.weiJianMian
    elif index == '联系率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvLianXi
    elif index == '解决率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvJieJue
    elif index == '满意率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvManYi
    elif index == '见面率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvJianMian
    elif index == '综合得分':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.deFen
    elif index == '区排名':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.quPaiMing
    elif index == '小组排名':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.xiaoZuPaiMing

# 数据列索引信息.xlsx
bookColIndex = openpyxl.load_workbook(fileColIndexInfo, data_only=True)
sheetColIndexKeShi = bookColIndex['科室']
maxRowColIndexKeShi = sheetColIndexKeShi.max_row
maxColColIndexKeShi = sheetColIndexKeShi.max_column
logger.debug('数据列索引信息.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowColIndexKeShi, maxColColIndexKeShi))
listColKeshi = {}
for rowIndex in range(2, maxRowColIndexKeShi + 1):
    valueKeShiName = sheetColIndexKeShi.cell(rowIndex, 1).value
    valueKeShiIndex = sheetColIndexKeShi.cell(rowIndex, 2).value
    if valueKeShiName is not None and valueKeShiName != '':
        listColKeshi[valueKeShiName] = valueKeShiIndex
logger.debug('{}'.format(listColKeshi))

sheetColIndexSheQu = bookColIndex['社区村']
maxRowColIndexSheQu = sheetColIndexSheQu.max_row
maxColColIndexSheQu = sheetColIndexSheQu.max_column
logger.debug('数据列索引信息.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowColIndexSheQu, maxColColIndexSheQu))
listColSheQu = {}
for rowIndex in range(2, maxRowColIndexSheQu + 1):
    valueSheQuName = sheetColIndexSheQu.cell(rowIndex, 1).value
    valueSheQuIndex = sheetColIndexSheQu.cell(rowIndex, 2).value
    if valueSheQuName is not None and valueSheQuName != '':
        listColSheQu[valueSheQuName] = valueSheQuIndex
logger.debug('{}'.format(listColSheQu))

# 打开 模板-本考核期裸分情况统计表.xlsx
# bookResultMuBan = openpyxl.load_workbook(fileResultMuBan, data_only=True)
bookResultMuBan = openpyxl.load_workbook(fileResultMuBan)
sheetResultKeShi = bookResultMuBan['科室']
maxRowResultMuBanKeShi = sheetResultKeShi.max_row
maxColResultMuBanKeShi = sheetResultKeShi.max_column
logger.debug('模板-月度得分统计.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowResultMuBanKeShi, maxColResultMuBanKeShi))

sheetResultSheQu = bookResultMuBan['社区村']
maxRowResultMuBanSheQu = sheetResultSheQu.max_row
maxColResultMuBanSheQu = sheetResultSheQu.max_column
logger.debug('模板-月度得分统计.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowResultMuBanSheQu, maxColResultMuBanSheQu))

strTitleSheQu = ''
strTitleKeShi = ''
strMain = ''
if timeCur.day < 19:
    strTitleKeShi = '{}月考核期拱辰街道机关科室裸分情况统计表\r\n（'.format(timeCur.month)
    strTitleSheQu = '{}月考核期拱辰街道社区（村）裸分情况统计表\r\n（'.format(timeCur.month)
    strMain = '{}月考核期（截至{}月{}日14时），'.format(timeCur.month, timeCur.month, timeCur.day)
    if timeCur.month == 1:
        strTitleKeShi = strTitleKeShi + '{}年12月19日0时至'.format(timeCur.year - 1)
        strTitleKeShi = strTitleKeShi + '{}年{}月{}日14时)'.format(timeCur.year, timeCur.month, timeCur.day)
        strTitleSheQu = strTitleSheQu + '{}年12月19日0时至'.format(timeCur.year - 1)
        strTitleSheQu = strTitleSheQu + '{}年{}月{}日14时)'.format(timeCur.year, timeCur.month, timeCur.day)
    else:
        strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month - 1)
        strTitleKeShi = strTitleKeShi + '{}月{}日14时)'.format(timeCur.month, timeCur.day)
        strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month - 1)
        strTitleSheQu = strTitleSheQu + '{}月{}日14时)'.format(timeCur.month, timeCur.day)
else:
    if timeCur.month == 12:
        strTitleKeShi = '1月考核期拱辰街道机关科室裸分情况统计表\r\n（'
        strTitleSheQu = '1月考核期拱辰街道社区（村）裸分情况统计表\r\n（'
        strMain = '1月考核期（截至{}年{}月{}日14时），'.format(timeCur.year, timeCur.month, timeCur.day)
    else:
        strTitleKeShi = '{}月考核期拱辰街道机关科室裸分情况统计表\r\n（'.format(timeCur.month + 1)
        strTitleSheQu = '{}月考核期拱辰街道社区（村）裸分情况统计表\r\n（'.format(timeCur.month + 1)
        strMain = '{}月考核期（截至{}月{}日14时），'.format(timeCur.month + 1, timeCur.month, timeCur.day)
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
    strTitleKeShi = strTitleKeShi + '{}月{}日14时)'.format(timeCur.month, timeCur.day)
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
    strTitleSheQu = strTitleSheQu + '{}月{}日14时)'.format(timeCur.month, timeCur.day)


def inset_not_none_info(is_ke_shi, title_name, insert_row, insert_value):
    global sheetResultKeShi
    global sheetResultSheQu
    if insert_value is not None:
        if title_name in listColKeshi or title_name in listColSheQu:
            if is_ke_shi == 1:
                sheetResultKeShi.cell(insert_row, listColKeshi[title_name]).value = insert_value
            else:
                sheetResultSheQu.cell(insert_row, listColSheQu[title_name]).value = insert_value


rowInsertKeShi = 4
rowInsertSheQu = 3
insetBuMenList = []
deFenSheQuLast = 0
paiMingSheQuLast = 0
insertSheQuCount = 0
deFenKeShiLast = 0
paiMingKeShiLast = 0
insertKeShiCount = 0
for buMen in listYouXiaoHuiFang.listGogDanInfo:
    insetBuMenList.append(buMen)
    if buMen in listChengBanSheQuInfo:
        insertSheQuCount = insertSheQuCount + 1
        inset_not_none_info(0, '接单部门', rowInsertSheQu, buMen)
        if buMen in listZongShu.listGogDanInfo:
            inset_not_none_info(0, '接单总量', rowInsertSheQu, listZongShu.listGogDanInfo[buMen].CountAll)
        if buMen in listNaRuShiJi.listGogDanInfo:
            inset_not_none_info(0, '纳入市级考核', rowInsertSheQu, listNaRuShiJi.listGogDanInfo[buMen].CountAll)
        inset_not_none_info(0, '区级有效回访', rowInsertSheQu, listYouXiaoHuiFang.listGogDanInfo[buMen].CountAll)
        inset_not_none_info(0, '响应率(%)', rowInsertSheQu, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvLianXi))
        inset_not_none_info(0, '解决率(%)', rowInsertSheQu, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvJieJue))
        inset_not_none_info(0, '满意率(%)', rowInsertSheQu, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvManYi))
        inset_not_none_info(0, '见面率(%)', rowInsertSheQu, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvJianMian))
        deFenCur = round(listYouXiaoHuiFang.listGogDanInfo[buMen].deFen)
        inset_not_none_info(0, '得分', rowInsertSheQu, deFenCur)
        if deFenCur != deFenSheQuLast:
            inset_not_none_info(0, '排名', rowInsertSheQu, insertSheQuCount)
            deFenSheQuLast = deFenCur
            paiMingSheQuLast = insertSheQuCount
        else:
            inset_not_none_info(0, '排名', rowInsertSheQu, paiMingSheQuLast)
        inset_not_none_info(0, '所属片区', rowInsertSheQu, listYouXiaoHuiFang.listGogDanInfo[buMen].suoShuXiaQu)
        rowInsertSheQu = rowInsertSheQu + 1
    else:
        insertKeShiCount = insertKeShiCount + 1
        inset_not_none_info(1, '接单部门', rowInsertKeShi, buMen)
        if buMen in listZongShu.listGogDanInfo:
            inset_not_none_info(1, '接单总量', rowInsertKeShi, listZongShu.listGogDanInfo[buMen].CountAll)
        if buMen in listNaRuShiJi.listGogDanInfo:
            inset_not_none_info(1, '纳入市级考核', rowInsertKeShi, listNaRuShiJi.listGogDanInfo[buMen].CountAll)
        inset_not_none_info(1, '区级有效回访', rowInsertKeShi, listYouXiaoHuiFang.listGogDanInfo[buMen].CountAll)
        inset_not_none_info(1, '响应率(%)', rowInsertKeShi, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvLianXi))
        inset_not_none_info(1, '解决率(%)', rowInsertKeShi, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvJieJue))
        inset_not_none_info(1, '满意率(%)', rowInsertKeShi, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvManYi))
        inset_not_none_info(1, '见面率(%)', rowInsertKeShi, round(listYouXiaoHuiFang.listGogDanInfo[buMen].lvJianMian))
        deFenCur = round(listYouXiaoHuiFang.listGogDanInfo[buMen].deFen)
        inset_not_none_info(1, '得分', rowInsertKeShi, deFenCur)
        if deFenCur != deFenKeShiLast:
            inset_not_none_info(1, '排名', rowInsertKeShi, insertKeShiCount)
            deFenKeShiLast = deFenCur
            paiMingKeShiLast = insertKeShiCount
        else:
            inset_not_none_info(1, '排名', rowInsertKeShi, paiMingKeShiLast)
        rowInsertKeShi = rowInsertKeShi + 1

listChengBanOnlyJieDan = []
for buMen in listNaRuShiJi.listGogDanInfo:
    if buMen not in insetBuMenList:
        if buMen in listChengBanSheQuInfo:
            inset_not_none_info(0, '接单部门', rowInsertSheQu, buMen)
            listChengBanOnlyJieDan.append(buMen)
            if buMen in listZongShu.listGogDanInfo:
                inset_not_none_info(0, '接单总量', rowInsertSheQu, listZongShu.listGogDanInfo[buMen].CountAll)
            inset_not_none_info(0, '纳入市级考核', rowInsertSheQu, listNaRuShiJi.listGogDanInfo[buMen].CountAll)
            inset_not_none_info(0, '所属片区', rowInsertSheQu, listNaRuShiJi.listGogDanInfo[buMen].suoShuXiaQu)
            rowInsertSheQu = rowInsertSheQu + 1
        else:
            inset_not_none_info(1, '接单部门', rowInsertKeShi, buMen)
            if buMen in listZongShu.listGogDanInfo:
                inset_not_none_info(1, '接单总量', rowInsertKeShi, listZongShu.listGogDanInfo[buMen].CountAll)
            inset_not_none_info(1, '纳入市级考核', rowInsertKeShi, listNaRuShiJi.listGogDanInfo[buMen].CountAll)
            rowInsertKeShi = rowInsertKeShi + 1

rowIndex = 3
while rowIndex < sheetResultSheQu.max_row - 1:
    valueName = '{}'.format(sheetResultSheQu.cell(rowIndex, listColSheQu['接单部门']).value).strip()
    if valueName == '' or valueName == 'None':
        sheetResultSheQu.delete_rows(rowIndex)
    else:
        rowIndex = rowIndex + 1

hanchxl.merged_cell_value(sheetResultSheQu, 1, sheetResultSheQu.max_row - 1, sheetResultSheQu.max_row - 1, listColSheQu['接单部门'])
hanchxl.merged_cell_value(sheetResultSheQu, 1, sheetResultSheQu.max_row, sheetResultSheQu.max_row, listColSheQu['所属片区'])
sheetResultSheQu.row_dimensions[sheetResultSheQu.max_row].height = 100
colNum = get_column_letter(listColSheQu['接单总量'])
sheetResultSheQu['{}{}'.format(colNum, sheetResultSheQu.max_row - 1)] = '=SUM({}3:{}{})'.format(colNum, colNum, sheetResultSheQu.max_row - 2)
colNum = get_column_letter(listColSheQu['纳入市级考核'])
sheetResultSheQu['{}{}'.format(colNum, sheetResultSheQu.max_row - 1)] = '=SUM({}3:{}{})'.format(colNum, colNum, sheetResultSheQu.max_row - 2)
colNum = get_column_letter(listColSheQu['区级有效回访'])
sheetResultSheQu['{}{}'.format(colNum, sheetResultSheQu.max_row - 1)] = '=SUM({}3:{}{})'.format(colNum, colNum, sheetResultSheQu.max_row - 2)

rowIndex = 3
colNum = get_column_letter(listColSheQu['排名'])
paiMingLast = 1
paiMingMergedStart = 3
for rowIndex in range(3, sheetResultSheQu.max_row - 1):
    paiMingCur = sheetResultSheQu.cell(rowIndex, listColSheQu['排名']).value
    if paiMingCur is None or paiMingCur == '':
        break
    if paiMingCur != paiMingLast:
        if rowIndex - paiMingMergedStart > 1:
            hanchxl.merged_cell_value(sheetResultSheQu, listColSheQu['排名'], paiMingMergedStart, rowIndex - 1)
        paiMingMergedStart = rowIndex
        paiMingLast = paiMingCur

rowIndex = 3
while rowIndex < sheetResultKeShi.max_row - 1:
    valueName = '{}'.format(sheetResultKeShi.cell(rowIndex, listColKeshi['接单部门']).value).strip()
    if valueName == '' or valueName == 'None':
        sheetResultKeShi.delete_rows(rowIndex)
    else:
        rowIndex = rowIndex + 1

hanchxl.merged_cell_value(sheetResultKeShi, 1, sheetResultKeShi.max_row - 1, sheetResultKeShi.max_row - 1, listColKeshi['接单部门'])
hanchxl.merged_cell_value(sheetResultKeShi, 1, sheetResultKeShi.max_row, sheetResultKeShi.max_row, listColKeshi['得分'] + 1)
sheetResultKeShi.row_dimensions[sheetResultKeShi.max_row].height = 100
colNum = get_column_letter(listColKeshi['接单总量'])
sheetResultKeShi['{}{}'.format(colNum, sheetResultKeShi.max_row - 1)] = '=SUM({}3:{}{})'.format(colNum, colNum, sheetResultKeShi.max_row - 2)
colNum = get_column_letter(listColKeshi['纳入市级考核'])
sheetResultKeShi['{}{}'.format(colNum, sheetResultKeShi.max_row - 1)] = '=SUM({}3:{}{})'.format(colNum, colNum, sheetResultKeShi.max_row - 2)
colNum = get_column_letter(listColKeshi['区级有效回访'])
sheetResultKeShi['{}{}'.format(colNum, sheetResultKeShi.max_row - 1)] = '=SUM({}3:{}{})'.format(colNum, colNum, sheetResultKeShi.max_row - 2)

rowIndex = 3
colNum = get_column_letter(listColKeshi['排名'])
paiMingLast = 1
paiMingMergedStart = 3
for rowIndex in range(3, sheetResultKeShi.max_row - 1):
    paiMingCur = sheetResultKeShi.cell(rowIndex, listColKeshi['排名']).value
    if paiMingCur is None or paiMingCur == '':
        break
    if paiMingCur != paiMingLast:
        if rowIndex - paiMingMergedStart > 1:
            hanchxl.merged_cell_value(sheetResultKeShi, listColKeshi['排名'], paiMingMergedStart, rowIndex - 1)
        paiMingMergedStart = rowIndex
        paiMingLast = paiMingCur

# 打开 今日接单量-区.xlsx
bookJieDanToday = openpyxl.load_workbook(fileJieDanToday, data_only=True)
sheetJieDanToday = bookJieDanToday.active
maxRowJieDanToday = sheetJieDanToday.max_row
maxColJieDanToday = sheetJieDanToday.max_column
logger.debug('今日接单量-区.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDanToday, maxColJieDanToday))

listJieDanToday = loadExcelFileByColumn.GongDanCountInFoList('今日接单量-区')
listJieDanToday.load_gong_dan_info_by_column(sheetJieDanToday, sheetQuanBu, listReplaceBuMen, 0)
logger.debug('{} 总量：{} '.format(listJieDanToday.name, listJieDanToday.CountAll))

bookJieDanToday.save(fileJieDanTodaySave)
logger.debug('保存  {}'.format(fileJieDanTodaySave))

# 打开 模板-新增接单三件以上部门情况统计表.xlsx
bookJieDanInfoMuBan = openpyxl.load_workbook(fileJieDanInfoMuBan)
sheetJieDanInfo = bookJieDanInfoMuBan.active
maxRowJieDanInfo = sheetJieDanInfo.max_row
maxColJieDanInfo = sheetJieDanInfo.max_column
logger.debug('模板-新增接单三件以上部门情况统计表.xlsx ------行数：{}行    列数：{}列'.format(maxRowJieDanInfo, maxColJieDanInfo))

strTitleJieDanSave = '{}月{}日新增接单三件以上部门情况统计表'.format(timeCur.month, timeCur.day)
sheetJieDanInfo.cell(2, 1).value = strTitleJieDanSave

colChengBan = hanchxl.get_col_index(sheetJieDanInfo, '承办单位', 3)
colShuLiang = hanchxl.get_col_index(sheetJieDanInfo, '数量', 3)
colYiBanJie = hanchxl.get_col_index(sheetJieDanInfo, '已办结', 3)
colBanLiZhong = hanchxl.get_col_index(sheetJieDanInfo, '办理中', 3)
rowInsert = 4
for buMen in listJieDanToday.listGogDanInfo:
    if listJieDanToday.listGogDanInfo[buMen].CountAll >= 3:
        sheetJieDanInfo.cell(rowInsert, colChengBan).value = listJieDanToday.listGogDanInfo[buMen].name
        sheetJieDanInfo.cell(rowInsert, colShuLiang).value = listJieDanToday.listGogDanInfo[buMen].CountAll
        rowInsert = rowInsert + 1

rowIndex = 3
while rowIndex < sheetJieDanInfo.max_row:
    valueName = '{}'.format(sheetJieDanInfo.cell(rowIndex, colChengBan).value).strip()
    if valueName == '' or valueName == 'None':
        sheetJieDanInfo.delete_rows(rowIndex)
    else:
        rowIndex = rowIndex + 1

hanchxl.merged_cell_value(sheetJieDanInfo, 1, sheetJieDanInfo.max_row, sheetJieDanInfo.max_row, colChengBan)
colNum = get_column_letter(colShuLiang)
sheetJieDanInfo['{}{}'.format(colNum, sheetJieDanInfo.max_row)] = '=SUM({}4:{}{})'.format(colNum, colNum, sheetJieDanInfo.max_row - 1)
colNum = get_column_letter(colYiBanJie)
sheetJieDanInfo['{}{}'.format(colNum, sheetJieDanInfo.max_row)] = '=SUM({}4:{}{})'.format(colNum, colNum, sheetJieDanInfo.max_row - 1)
colNum = get_column_letter(colBanLiZhong)
sheetJieDanInfo['{}{}'.format(colNum, sheetJieDanInfo.max_row)] = '=SUM({}4:{}{})'.format(colNum, colNum, sheetJieDanInfo.max_row - 1)

bookJieDanInfoMuBan.save(fileJieDanInfoSave)
logger.debug('保存  {}'.format(fileJieDanInfoSave))

jieDanTodayInfo = jieDanInFo()
jieDanTodayInfo.jieDanCount = listJieDanToday.CountAll
jieDanTodayInfo.jieDanShiPai = listJieDanToday.CountShi
jieDanTodayInfo.jieDanQuPai = listJieDanToday.CountQu

for buMen in listJieDanToday.listGogDanInfo:
    if buMen in listChengBanSheQuInfo:
        jieDanTodayInfo.jieDanSheQu = jieDanTodayInfo.jieDanSheQu + 1
    else:
        jieDanTodayInfo.jieDanKeShi = jieDanTodayInfo.jieDanKeShi + 1

sheetCountRiSuQiu = bookRiHuiZong['日诉求量']
maxRowCountRiSuQiu = sheetCountRiSuQiu.max_row
maxColCountRiSuQiu = sheetCountRiSuQiu.max_column
logger.debug('{} 日诉求量 -------行数：{}行    列数：{}列'.format(fileRiHuiZong, maxRowCountRiSuQiu, maxColCountRiSuQiu))

dateYesterdayString = '{}月{}日'.format(datatimeYesterday.month, datatimeYesterday.day)

colTimestamp = hanchxl.get_col_index(sheetCountRiSuQiu, '时间戳')
colDate = hanchxl.get_col_index(sheetCountRiSuQiu, '日期')
colRiSuQiu = hanchxl.get_col_index(sheetCountRiSuQiu, '日诉求量')
colRiQuPai = hanchxl.get_col_index(sheetCountRiSuQiu, '区派')
colRiShiPai = hanchxl.get_col_index(sheetCountRiSuQiu, '市派')
colRiKeShi = hanchxl.get_col_index(sheetCountRiSuQiu, '科室承办')
colRiSheQu = hanchxl.get_col_index(sheetCountRiSuQiu, '社区（村）承办')
if colDate is not None:
    for row in range(2, maxRowCountRiSuQiu + 1):
        valueDate = '{}'.format(sheetCountRiSuQiu.cell(row, colDate).value).strip()
        logger.debug('{}  {}'.format(valueDate, dateYesterdayString))
        if valueDate == dateYesterdayString:
            jieDanTodayInfo.jieDanYesterday = sheetCountRiSuQiu.cell(row, colRiSuQiu).value

sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colTimestamp).value = timeCurStr
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colDate).value = '{}月{}日'.format(timeCur.month, timeCur.day)
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiSuQiu).value = jieDanTodayInfo.jieDanCount
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiQuPai).value = jieDanTodayInfo.jieDanQuPai
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiShiPai).value = jieDanTodayInfo.jieDanShiPai
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiKeShi).value = jieDanTodayInfo.jieDanKeShi
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiSheQu).value = jieDanTodayInfo.jieDanSheQu

logger.debug('承办部门-排序前：{}'.format(listJieDanToday.listGogDanInfo))
sheetCountChengBan = bookRiHuiZong['承办部门接单量']
maxRow = sheetCountChengBan.max_row
colTimestamp = hanchxl.get_col_index(sheetCountChengBan, '时间戳')
colDate = hanchxl.get_col_index(sheetCountChengBan, '日期')
colJieDanCount = hanchxl.get_col_index(sheetCountChengBan, '接单总量')

sheetCountChengBan.cell(maxRow + 1, colTimestamp).value = timeCurStr
sheetCountChengBan.cell(maxRow + 1, colDate).value = jieDanTodayInfo.date
sheetCountChengBan.cell(maxRow + 1, colRiSuQiu).value = jieDanTodayInfo.jieDanCount
ChengBanCount = []
ChengBanCountToday = []
maxChengBanCount = 0
for index in listJieDanToday.listGogDanInfo:
    maxCol = sheetCountChengBan.max_column
    indexChengBanSearch = hanchxl.get_col_index(sheetCountChengBan, index)
    if indexChengBanSearch is None:
        indexChengBanSearch = maxCol + 1
        sheetCountChengBan.cell(1, indexChengBanSearch).value = index
    sheetCountChengBan.cell(maxRow + 1, indexChengBanSearch).value = listJieDanToday.listGogDanInfo[index].CountAll
    if listJieDanToday.listGogDanInfo[index].CountAll >= 5:
        if maxChengBanCount < 10:
            ChengBanCount.append(index)
    maxChengBanCount = maxChengBanCount + 1
    logger.debug('{} 有 {} 件 在{}列'.format(index, listJieDanToday.listGogDanInfo[index].CountAll, indexChengBanSearch))
logger.debug('{}'.format(ChengBanCount))

maxRow = sheetCountChengBan.max_row
ChengBanCountYesterday = {}
colDate = hanchxl.get_col_index(sheetCountChengBan, '日期')
yesterdayStr = '{}-{}-{}'.format(datatimeYesterday.year, datatimeYesterday.month, datatimeYesterday.day)
for row in range(2, maxRow + 1):
    valueDate = '{}'.format(sheetCountChengBan.cell(row, colDate).value).strip()
    logger.debug('{}  {}'.format(valueDate, yesterdayStr))
    if valueDate == yesterdayStr:
        for index in ChengBanCount:
            colChengBan = hanchxl.get_col_index(sheetCountChengBan, index)
            if colChengBan is not None:
                valueChengBan = sheetCountChengBan.cell(row, colChengBan).value
                if valueChengBan is not None:
                    ChengBanCountYesterday[index] = valueChengBan
                else:
                    ChengBanCountYesterday[index] = 0
            else:
                ChengBanCountYesterday[index] = 0
logger.debug('ChengBanCountYesterday:{}'.format(ChengBanCountYesterday))

# 打开 每日必办-回访工单明细.xlsx
bookHuiFang = openpyxl.load_workbook(fileHuiFangCount, data_only=True)
sheetHuiFang = bookHuiFang.active
maxRowHuiFang = sheetHuiFang.max_row
maxColHuiFang = sheetHuiFang.max_column
logger.debug('每日必办-回访工单明细.xlsx -------行数：{}行    列数：{}列'.format(maxRowHuiFang, maxColHuiFang))

listHuiFangToday = loadExcelFileByColumn.GongDanCountInFoList('回访工单明细')
listHuiFangToday.load_gong_dan_info_by_column(sheetHuiFang, sheetQuanBu, listReplaceBuMen, 2)
logger.debug('{} 总量：{} '.format(listHuiFangToday.name, listHuiFangToday.CountAll))

bookHuiFang.save(fileHuiFangChengBan)
logger.debug('保存  {}'.format(fileHuiFangChengBan))

infoChengBanYouXiao = infoChengBanGongDan.get_cheng_ban_Ban_li_info(sheetHuiFang)
infoChengBanYouXiaoSort = {}
countYouXiaoHuiFang = 0
for index in infoChengBanYouXiao:
    infoChengBanYouXiaoSort[index] = infoChengBanYouXiao[index].countKaoHe
    countYouXiaoHuiFang = countYouXiaoHuiFang + infoChengBanYouXiao[index].countKaoHe
infoChengBanYouXiaoSort = sorted(infoChengBanYouXiaoSort.items(), key=lambda kv: kv[1], reverse=True)

# 打开 模板-工单回访情况.xlsx
bookResultShiFenInfo = openpyxl.load_workbook(fileResultShiFenInfo, data_only=True)
sheetShiFenInfo = bookResultShiFenInfo.active
maxRowShiFenInfo = sheetShiFenInfo.max_row
maxColShiFenInfo = sheetShiFenInfo.max_column
logger.debug('模板-工单回访情况.xlsx -------行数：{}行    列数：{}列'.format(maxRowShiFenInfo, maxColShiFenInfo))

strTitleHuiFang = '{}至{}月{}日9时工单回访情况（{}件）'.format(strHuiFangStart, timeCur.month, timeCur.day, listHuiFangToday.CountYouXiaoHuiFang)
sheetShiFenInfo.cell(2, 1).value = strTitleHuiFang

colIndexChengBan = hanchxl.get_col_index(sheetShiFenInfo, '承办部门', 3)
colIndexBanJie = hanchxl.get_col_index(sheetShiFenInfo, '办结量', 3)
colIndexKaoHe = hanchxl.get_col_index(sheetShiFenInfo, '纳入考核', 3)
colIndexCodeID = hanchxl.get_col_index(sheetShiFenInfo, '工单编号', 3)
colIndexTitle = hanchxl.get_col_index(sheetShiFenInfo, '标题', 3)
colIndexHuiFang = hanchxl.get_col_index(sheetShiFenInfo, '回访结果', 3)
colIndexlvLianXi = hanchxl.get_col_index(sheetShiFenInfo, '联系率', 3)
colIndexlvJieJue = hanchxl.get_col_index(sheetShiFenInfo, '解决率', 3)
colIndexlvManYi = hanchxl.get_col_index(sheetShiFenInfo, '满意率', 3)
colIndexlvJianMian = hanchxl.get_col_index(sheetShiFenInfo, '见面率', 3)
insertIndex = 4
for index in infoChengBanYouXiaoSort:
    mergeRowStart = insertIndex
    index = index[0]
    listGongDanQu = infoChengBanYouXiao[index].gongDanListQu
    for indexQu in listGongDanQu:
        sheetShiFenInfo.cell(insertIndex, colIndexChengBan).value = index
        sheetShiFenInfo.cell(insertIndex, colIndexBanJie).value = infoChengBanYouXiao[index].countBanJie
        sheetShiFenInfo.cell(insertIndex, colIndexKaoHe).value = infoChengBanYouXiao[index].countKaoHe
        sheetShiFenInfo.cell(insertIndex, colIndexCodeID).value = indexQu
        sheetShiFenInfo.cell(insertIndex, colIndexTitle).value = listGongDanQu[indexQu].biaoTi
        sheetShiFenInfo.cell(insertIndex, colIndexHuiFang).value = listGongDanQu[indexQu].huiFangJieGuo
        sheetShiFenInfo.cell(insertIndex, colIndexlvLianXi).value = infoChengBanYouXiao[index].lvLianXi
        sheetShiFenInfo.cell(insertIndex, colIndexlvJieJue).value = infoChengBanYouXiao[index].lvJieJue
        sheetShiFenInfo.cell(insertIndex, colIndexlvManYi).value = infoChengBanYouXiao[index].lvManYi
        sheetShiFenInfo.cell(insertIndex, colIndexlvJianMian).value = infoChengBanYouXiao[index].lvJianMian
        insertIndex = insertIndex + 1

    listGongDanShi = infoChengBanYouXiao[index].gongDanListShi
    for indexQu in listGongDanShi:
        sheetShiFenInfo.cell(insertIndex, colIndexChengBan).value = index
        sheetShiFenInfo.cell(insertIndex, colIndexBanJie).value = infoChengBanYouXiao[index].countBanJie
        sheetShiFenInfo.cell(insertIndex, colIndexKaoHe).value = infoChengBanYouXiao[index].countKaoHe
        sheetShiFenInfo.cell(insertIndex, colIndexCodeID).value = indexQu
        sheetShiFenInfo.cell(insertIndex, colIndexTitle).value = listGongDanShi[indexQu].biaoTi
        sheetShiFenInfo.cell(insertIndex, colIndexHuiFang).value = listGongDanShi[indexQu].huiFangJieGuo
        sheetShiFenInfo.cell(insertIndex, colIndexlvLianXi).value = infoChengBanYouXiao[index].lvLianXi
        sheetShiFenInfo.cell(insertIndex, colIndexlvJieJue).value = infoChengBanYouXiao[index].lvJieJue
        sheetShiFenInfo.cell(insertIndex, colIndexlvManYi).value = infoChengBanYouXiao[index].lvManYi
        sheetShiFenInfo.cell(insertIndex, colIndexlvJianMian).value = infoChengBanYouXiao[index].lvJianMian
        insertIndex = insertIndex + 1
    mergeRowEnd = insertIndex - 1
    if mergeRowEnd - mergeRowStart >= 1:
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexChengBan, mergeRowStart, mergeRowEnd)
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexBanJie, mergeRowStart, mergeRowEnd)
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexKaoHe, mergeRowStart, mergeRowEnd)
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexlvLianXi, mergeRowStart, mergeRowEnd)
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexlvJieJue, mergeRowStart, mergeRowEnd)
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexlvManYi, mergeRowStart, mergeRowEnd)
        hanchxl.merged_cell_value(sheetShiFenInfo, colIndexlvJianMian, mergeRowStart, mergeRowEnd)

# 保存文件
fileHuiFangInfoSave = dirPath + '\\[内部] 附件4 {}月{}日9时至{}月{}日9时工单回访情况（{}件）.xlsx'.format(datatimeYesterday.month, datatimeYesterday.day, timeCur.month, timeCur.day, countYouXiaoHuiFang)
bookResultShiFenInfo.save(fileHuiFangInfoSave)

tuTwoCurrentYear = []
tuTwoLastYear = []
tuTwobeforeLastYear  = []
strGongNuan = ''
# # 打开 今日接单量-街道.xlsx
# if os.path.exists(fileJieDanTodayJieDao):
#     bookJieDanTodayJieDao = openpyxl.load_workbook(fileJieDanTodayJieDao, data_only=True)
#     sheetJieDanTodayJieDao = bookJieDanTodayJieDao.active
#     maxRowJieDanTodayJieDao = sheetJieDanTodayJieDao.max_row
#     maxColJieDanTodayJieDao = sheetJieDanTodayJieDao.max_column
#     logger.debug('今日接单量-街道.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDanTodayJieDao, maxColJieDanTodayJieDao))
#
#     countGongNuan = 0
#     colBiaoQian = hanchxl.get_col_index(sheetJieDanTodayJieDao, '标签')
#     for row in range(2, maxRowJieDanTodayJieDao + 1):
#         valueBiaoQian = '{}'.format(sheetJieDanTodayJieDao.cell(row, colBiaoQian).value).strip()
#         if valueBiaoQian == '2':
#             countGongNuan = countGongNuan + 1
#
#     if countGongNuan == 0:
#         strGongNuan = '未接收供暖类工单。'
#     else:
#         strGongNuan = '共接收供暖类工单{}件,主要反映 。'.format(countGongNuan)
#
#     maxRowGongNuanInfo2024 = sheetGongNuanInfo2024.max_row
#     colTimestampGongNuan = hanchxl.get_col_index(sheetGongNuanInfo2024, '时间戳')
#     colDateGongNuan = hanchxl.get_col_index(sheetGongNuanInfo2024, '日期')
#     colCountGongNuan = hanchxl.get_col_index(sheetGongNuanInfo2024, '工单量')
#
#     sheetGongNuanInfo2024.cell(maxRowGongNuanInfo2024 + 1, colTimestampGongNuan).value = timeCurStr
#     sheetGongNuanInfo2024.cell(maxRowGongNuanInfo2024 + 1, colDateGongNuan).value = '{}月{}日'.format(timeCur.month, timeCur.day)
#     sheetGongNuanInfo2024.cell(maxRowGongNuanInfo2024 + 1, colCountGongNuan).value = countGongNuan
#     bookGongNuanInfo.save(fileGongNuanInfo)

# 修改 每日必办 数据
for para in docMuBan.paragraphs:
    for run in para.runs:
        logger.debug('{}'.format(run.text))
        if '{明日日期}' in run.text:
            run.text = run.text.replace('{明日日期}', strTomorrow)
        if '{考核期开始日期}' in run.text:
            run.text = run.text.replace('{考核期开始日期}', strKaoHeStartDay)
        if '{今日日期}' in run.text:
            run.text = run.text.replace('{今日日期}', strToday)
        if '{回访开始日期}' in run.text:
            run.text = run.text.replace('{回访开始日期}', strHuiFangStart)
        if '{回访截止日期}' in run.text:
            run.text = run.text.replace('{回访截止日期}', '{}月{}日9时'.format(timeCur.month, timeCur.day))
        if '{办结工单总量}' in run.text:
            run.text = run.text.replace('{办结工单总量}', '{}'.format(listHuiFangToday.CountAll))
        if '{办结总量区派}' in run.text:
            run.text = run.text.replace('{办结总量区派}', '{}'.format(listHuiFangToday.CountQu))
        if '{办结总量市派}' in run.text:
            run.text = run.text.replace('{办结总量市派}', '{}'.format(listHuiFangToday.CountShi))
        if '{办结考核总量}' in run.text:
            run.text = run.text.replace('{办结考核总量}', '{}'.format(listHuiFangToday.CountYouXiaoHuiFang))
        if '{办结考核区派}' in run.text:
            run.text = run.text.replace('{办结考核区派}', '{}'.format(listHuiFangToday.CountYouXiaoHuiFangQu))
        if '{办结考核市派}' in run.text:
            run.text = run.text.replace('{办结考核市派}', '{}'.format(listHuiFangToday.CountYouXiaoHuiFangShi))
        if '{办结联系率}' in run.text:
            run.text = run.text.replace('{办结联系率}', '{}'.format(int(round(listHuiFangToday.lvLianXi))))
        if '{办结解决率}' in run.text:
            run.text = run.text.replace('{办结解决率}', '{}'.format(int(round(listHuiFangToday.lvJieJue))))
        if '{办结满意率}' in run.text:
            run.text = run.text.replace('{办结满意率}', '{}'.format(int(round(listHuiFangToday.lvManYi))))
        if '{办结见面率}' in run.text:
            run.text = run.text.replace('{办结见面率}', '{}'.format(int(round(listHuiFangToday.lvJianMian))))
        if '{失分工单件数}' in run.text:
            run.text = run.text.replace('{失分工单件数}', '{}'.format(listHuiFangToday.CountYouXiaoHuiFang - listHuiFangToday.CountShuangMan))
        if '{接单开始日期}' in run.text:
            run.text = run.text.replace('{接单开始日期}', '{}月{}日'.format(datatimeYesterday.month, datatimeYesterday.day))
        if '{接单结束日期}' in run.text:
            run.text = run.text.replace('{接单结束日期}', '{}月{}日'.format(timeCur.month, timeCur.day))
        if '{今日接单量}' in run.text:
            run.text = run.text.replace('{今日接单量}', '{}'.format(jieDanTodayInfo.jieDanCount))
        if '{今日区派}' in run.text:
            run.text = run.text.replace('{今日区派}', '{}'.format(jieDanTodayInfo.jieDanQuPai))
        if '{今日市派}' in run.text:
            run.text = run.text.replace('{今日市派}', '{}'.format(jieDanTodayInfo.jieDanShiPai))
        if '{前一日接单量}' in run.text:
            run.text = run.text.replace('{前一日接单量}', '{}'.format(jieDanTodayInfo.jieDanYesterday))
        if '{接单变化量}' in run.text:
            strReplace = ''
            if jieDanTodayInfo.jieDanCount > jieDanTodayInfo.jieDanYesterday:
                strReplace = '增加{}'.format(abs(jieDanTodayInfo.jieDanCount - jieDanTodayInfo.jieDanYesterday))
            else:
                strReplace = '减少{}'.format(abs(jieDanTodayInfo.jieDanCount - jieDanTodayInfo.jieDanYesterday))
            run.text = run.text.replace('{接单变化量}', strReplace)
        if '{环比结果}' in run.text:
            numChaZhi = jieDanTodayInfo.jieDanCount - jieDanTodayInfo.jieDanYesterday
            strReplace = ''
            if jieDanTodayInfo.jieDanYesterday != 0:
                if numChaZhi > 0:
                    strReplace = '上升{}'.format(abs(round(numChaZhi*100/jieDanTodayInfo.jieDanYesterday, 1)))
                else:
                    strReplace = '下降{}'.format(abs(round(numChaZhi*100/jieDanTodayInfo.jieDanYesterday, 1)))
            run.text = run.text.replace('{环比结果}', strReplace)
        if '{供暖工单件数}' in run.text:
            run.text = run.text.replace('{供暖工单件数}', strGongNuan)

docMuBan.save(fileMustDoSave)

bookRiHuiZong.save(fileRiHuiZong)


xiaoZuList = ['拱辰街道', '城关街道', '窦店镇', '西潞街道', '阎村镇', '长阳镇', '琉璃河镇', '青龙湖镇']
saveTitleList = ['工单\n总量', '工单量\n目标值', '双满数', '双满占比', '全量\n解决率', '区级\n有效回访', '解决率', '满意率', '综合评分', '差值', '见面率']

colListMuBan = {}
colList = {}
for index in saveTitleList:
    colList[index] = hanchxl.get_col_index(sheetSanLv, index, 2)
    colListMuBan[index] = hanchxl.get_col_index(sheetSanLvMuBan, index, 3)
    # logger.debug('数据{}:{}   保存：{}:{}'.format(index, colList[index], index, colListMuBan[index]))

colXuHao = hanchxl.get_col_index(sheetSanLv, '序号', 2)
colName = hanchxl.get_col_index(sheetSanLv, '名称', 2)

colNameSave = hanchxl.get_col_index(sheetSanLvMuBan, '承办单位', 3)
colPaiMingSave = hanchxl.get_col_index(sheetSanLvMuBan, '裸分\n排名', 3)
rowInsert = 4
for row in range(3, maxRowSanLv + 1):
    valueName = '{}'.format(sheetSanLv.cell(row, colName).value).strip()
    if valueName in xiaoZuList:
        sheetSanLvMuBan.cell(rowInsert, colNameSave).value = valueName
        # valuePaiMing = '{}'.format(int(sheetSanLv.cell(row, colXuHao).value))
        valuePaiMing = '{}'.format(row - 2)
        sheetSanLvMuBan.cell(rowInsert, colPaiMingSave).value = valuePaiMing
        for index in saveTitleList:
            logger.debug('{}  {}： {}'.format(valueName, index, sheetSanLv.cell(row, colList[index]).value))
            sheetSanLvMuBan.cell(rowInsert, colListMuBan[index]).value = sheetSanLv.cell(row, colList[index]).value
        if valueName == '拱辰街道':
            valueJieJue = '{}'.format(sheetSanLv.cell(row, colList['解决率']).value).strip('%')
            if valueJieJue != '-':
                countInfo.lvJieJue = round(float(valueJieJue), 0)
            else:
                countInfo.lvJieJue = '-'
            valueManYi = '{}'.format(sheetSanLv.cell(row, colList['满意率']).value).strip('%')
            if valueManYi != '-':
                countInfo.lvManYi = round(float(valueManYi), 0)
            else:
                countInfo.lvManYi = '-'
            valueJianMain = '{}'.format(sheetSanLv.cell(row, colList['见面率']).value).strip('%')
            if valueJianMain != '-':
                countInfo.lvJianMian = round(float(valueJianMain), 0)
            else:
                countInfo.lvJianMian = '-'
            valueDeFen = '{}'.format(sheetSanLv.cell(row, colList['综合评分']).value).strip('%')
            if valueDeFen != '-':
                countInfo.deFen = round(float(valueDeFen), 0)
            else:
                countInfo.deFen = '-'
            countInfo.quPaiMing = int(valuePaiMing)
            countInfo.xiaoZuPaiMing = rowInsert - 3
            strJieDan = '{}\n区派{}件\n市派{}件'.format(countInfo.jieDanCount, countInfo.jieDanQuPai, countInfo.jieDanShiPai)
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['工单\n总量']).value = strJieDan
            strShuangMan = '{}\n区派{}件\n市派{}件'.format(countInfo.CountShuangMan, countInfo.CountShuangManQu, countInfo.CountShuangManShi)
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['双满数']).value = strShuangMan
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['双满占比']).value = '{}%'.format(round(countInfo.CountShuangMan*100 / countInfo.jieDanCount, 2))
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['全量\n解决率']).value = '{}%'.format(round(countInfo.jieJue*100 / countInfo.jieDanCount, 2))
            strQuKaoHe = '{}\n区派{}件\n市派{}件'.format(countInfo.kaoHeCount, countInfo.kaoHeQuPai, countInfo.kaoHeShiPai)
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['区级\n有效回访']).value = strQuKaoHe
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['解决率']).value = '{}%'.format(listYouXiaoHuiFang.lvJieJue)
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['满意率']).value = '{}%'.format(listYouXiaoHuiFang.lvManYi)
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['见面率']).value = '{}%'.format(listYouXiaoHuiFang.lvJianMian)
            sheetSanLvMuBan.cell(rowInsert, colListMuBan['综合评分']).value = listYouXiaoHuiFang.deFen
            if listYouXiaoHuiFang.deFen > 80:
                sheetSanLvMuBan.cell(rowInsert, colListMuBan['差值']).value = '{}'.format(round(80 - listYouXiaoHuiFang.deFen, 2))
            else:
                sheetSanLvMuBan.cell(rowInsert, colListMuBan['差值']).value = '-{}'.format(round(80 - listYouXiaoHuiFang.deFen, 2))
        rowInsert = rowInsert + 1

sheetSanLvMuBan.cell(3, colListMuBan['工单\n总量']).value = '本评价期\n办结量'
bookSanLvMuBan.save(fileSanLvSave)

strMain = strMain + '我街道接收工单{}件'.format(listZongShu.CountAll)
strMain = strMain + '（区派{}件，市派{}件），'.format(listZongShu.CountQu, listZongShu.CountShi)
strMain = strMain + '纳入市级考核范围{}件'.format(listNaRuShiJi.CountAll)
strMain = strMain + '（区派{}件，市派{}件），'.format(listNaRuShiJi.CountQu, listNaRuShiJi.CountShi)
strMain = strMain + '区级有效回访{}件'.format(listYouXiaoHuiFang.CountAll)
strMain = strMain + '（区派{}件，市派{}件），'.format(listYouXiaoHuiFang.CountQu, listYouXiaoHuiFang.CountShi)
strMain = strMain + '联系{}件，'.format(listYouXiaoHuiFang.lianXi)
# strMain = strMain + '未联系{}件，'.format(listYouXiaoHuiFang.weiLanXi)
strMain = strMain + '联系率{}%；'.format(listYouXiaoHuiFang.lvLianXi)
strMain = strMain + '解决{}件，'.format(listYouXiaoHuiFang.jieJue)
# strMain = strMain + '未解决{}件，'.format(listYouXiaoHuiFang.weiJieJue)
strMain = strMain + '解决率{}%；'.format(listYouXiaoHuiFang.lvJieJue)
strMain = strMain + '满意{}件，'.format(listYouXiaoHuiFang.manYi)
strMain = strMain + '基本满意{}件，'.format(listYouXiaoHuiFang.jiBenManYi)
# strMain = strMain + '不满意{}件，'.format(listYouXiaoHuiFang.buManYi)
strMain = strMain + '满意率{}%；'.format(listYouXiaoHuiFang.lvManYi)
strMain = strMain + '见面{}件，'.format(listYouXiaoHuiFang.jianMian)
# strMain = strMain + '未见面{}件，'.format(listYouXiaoHuiFang.weiJianMian)
strMain = strMain + '见面率{}%；综合得分{}，'.format(listYouXiaoHuiFang.lvJianMian, listYouXiaoHuiFang.deFen)
strMain = strMain + '全区排名第{}名。'.format(countInfo.quPaiMing)

sheetResultKeShi.cell(2, 1).value = strTitleKeShi
sheetResultSheQu.cell(1, 1).value = strTitleSheQu

strMsgCount = strMain
strMainKeshi = '备注：\r\n1.未接单的机关科室未在上表内列出\r\n2.' + strMain
sheetResultKeShi.cell(sheetResultKeShi.max_row, 1).value = strMainKeshi
strMainSheQu = '备注：\r\n1.未接单的社区（村）未在上表内列出\r\n2.' + strMain
sheetResultSheQu.cell(sheetResultSheQu.max_row, 1).value = strMainSheQu

listChenBanJieDanIsNone = []
for nameChengban in listChengBanSheQu:
    if nameChengban not in insetBuMenList and nameChengban not in listChengBanOnlyJieDan:
        listChenBanJieDanIsNone.append(nameChengban)

strMainSheQu = ""
strChenBanNotNone = ""
if len(listChenBanJieDanIsNone) > 0:
    for nameChengban in listChenBanJieDanIsNone:
        if strChenBanNotNone == "":
            strChenBanNotNone = strChenBanNotNone + "{}".format(nameChengban)
        else:
            strChenBanNotNone = strChenBanNotNone + "、{}".format(nameChengban)
    strMainSheQu = '备注：\r\n1.零诉求社区（村）未在上表内列出（{}）\r\n2.'.format(strChenBanNotNone) + strMain
else:
    strMainSheQu = '备注：\r\n' + strMain
bookResultMuBan.save(fileResultSave)
logger.debug('保存 {} '.format(fileResultSave))

class countGongNuanInfo(object):
    def __init__(self):
        self.CountToday = 0
        self.CountYesterday = 0
        self.CountBeforYesterday = 0


# 准备 图1. 七日诉求量情况 数据
# 统计 七日诉求量 开始日期
dateCountList = {}
gongNuanCountList = {}
tuOneLabels = []
for dayJianGe in range(6, -1, -1):
    timeSearchDay = datetime.datetime.fromtimestamp(timeCur.timestamp() - 60 * 60 * 24 * dayJianGe).strftime('%Y-%m-%d')
    timeSearchDayStartStr = timeSearchDay + '  00:00:00'
    timeSearchDayEndStr = timeSearchDay + '  23:59:59'
    timeSearchDayStart = time.strptime(timeSearchDayStartStr, '%Y-%m-%d %H:%M:%S')
    timeSearchDayEnd = time.strptime(timeSearchDayEndStr, '%Y-%m-%d %H:%M:%S')
    dayCount = '{}月{}日'.format(timeSearchDayStart.tm_mon, timeSearchDayStart.tm_mday)
    logger.debug('{} 间隔 {} 天'.format(dayCount, dayJianGe))
    tuOneLabels.append(dayCount)
    dateCount = dateJieDanInFo()
    dateCountList[dayCount] = dateCount

    countGongNuan = countGongNuanInfo()
    gongNuanCountList[dayCount] = countGongNuan

logger.debug('{}'.format(tuOneLabels))

# 获取生成图1 的数据
maxRow = sheetCountRiSuQiu.max_row
colDate = hanchxl.get_col_index(sheetCountRiSuQiu, '日期')
colRiSuQiu = hanchxl.get_col_index(sheetCountRiSuQiu, '日诉求量')
colRiQuPai = hanchxl.get_col_index(sheetCountRiSuQiu, '区派')
colRiShiPai = hanchxl.get_col_index(sheetCountRiSuQiu, '市派')
for row in range(2, maxRow + 1):
    valueDate = '{}'.format(sheetCountRiSuQiu.cell(row, colDate).value).strip()
    if valueDate in tuOneLabels:
        if sheetCountRiSuQiu.cell(row, colRiSuQiu).value is not None:
            dateCountList[valueDate].count = sheetCountRiSuQiu.cell(row, colRiSuQiu).value
        else:
            dateCountList[valueDate].count = 0
        if sheetCountRiSuQiu.cell(row, colRiQuPai).value is not None:
            dateCountList[valueDate].quPai = sheetCountRiSuQiu.cell(row, colRiQuPai).value
        else:
            dateCountList[valueDate].quPai = 0
        if sheetCountRiSuQiu.cell(row, colRiShiPai).value is not None:
            dateCountList[valueDate].shiPai = sheetCountRiSuQiu.cell(row, colRiShiPai).value
        else:
            dateCountList[valueDate].shiPai = 0

# # 获取生成图2 的数据
# maxRow = sheetGongNuanInfo2024.max_row# colDate = hanchxl.get_col_index(sheetGongNuanInfo2024, '日期')
# colCount = hanchxl.get_col_index(sheetGongNuanInfo2024, '工单量')
# for row in range(2, maxRow + 1):
#     valueDate = '{}'.format(sheetGongNuanInfo2024.cell(row, colDate).value).strip()
#     if valueDate in tuOneLabels:
#         if sheetGongNuanInfo2024.cell(row, colCount).value is not None:
#             gongNuanCountList[valueDate].CountToday = int('{}'.format(sheetGongNuanInfo2024.cell(row, colCount).value))
#         else:
#             gongNuanCountList[valueDate].CountToday = 0

# maxRow = sheetGongNuanInfo2023.max_row
# colDate = hanchxl.get_col_index(sheetGongNuanInfo2023, '日期')
# colCount2023 = hanchxl.get_col_index(sheetGongNuanInfo2023, '2023年')
# colCount2022 = hanchxl.get_col_index(sheetGongNuanInfo2023, '2022年')
# for row in range(2, maxRow + 1):
#     valueDate = '{}'.format(sheetGongNuanInfo2023.cell(row, colDate).value).strip()
#     if valueDate in tuOneLabels:
#         if sheetGongNuanInfo2023.cell(row, colCount2023).value is not None:
#             gongNuanCountList[valueDate].CountYesterday = int('{}'.format(sheetGongNuanInfo2023.cell(row, colCount2023).value))
#         else:
#             gongNuanCountList[valueDate].CountYesterday = 0
#         if sheetGongNuanInfo2023.cell(row, colCount2022).value is not None:
#             gongNuanCountList[valueDate].CountBeforYesterday = int('{}'.format(sheetGongNuanInfo2023.cell(row, colCount2022).value))
#         else:
#             gongNuanCountList[valueDate].CountBeforYesterday = 0

# tuTwoCurrentYear = [2, 8, 3, 7, 5, 2, 3]
# tuTwoLastYear = [6, 10, 7, 1, 3, 1, 2]
# tuTwobeforeLastYear  = [4, 8, 2, 2, 3, 2, 3]

tuOneRiSuQiu = []
tuOneQuPai = []
tuOneShiPai = []

tuTwoCurrentYear = []
tuTwoLastYear = []
tuTwobeforeLastYear  = []
for index in tuOneLabels:
    tuOneRiSuQiu.append(dateCountList[index].count)
    tuOneQuPai.append(dateCountList[index].quPai)
    tuOneShiPai.append(dateCountList[index].shiPai)
#
#     tuTwoCurrentYear.append(gongNuanCountList[index].CountToday)
#     tuTwoLastYear.append(gongNuanCountList[index].CountYesterday)
#     tuTwobeforeLastYear.append(gongNuanCountList[index].CountBeforYesterday)

logger.debug('tuOneLabels:{}'.format(tuOneLabels))
logger.debug('tuOneRiSuQiu：{}'.format(tuOneRiSuQiu))
logger.debug('tuOneQuPai：{}'.format(tuOneQuPai))
logger.debug('tuOneShiPai：{}'.format(tuOneShiPai))
jsPath = dirPath + '\\pyecharts_assets\\'
GenerateCharts.do_zu_he_tu_image(tuOneLabels, tuOneQuPai, '区派', tuOneShiPai, '市派', tuOneRiSuQiu, '日诉求量',
                                fileImageTuOneSave, jsPath, '图1. 七日诉求量情况')

# logger.debug('准备生成 图2. 七日内供暖工单日诉求量.png')
# logger.debug('tuOneLabels:{}'.format(tuOneLabels))
# logger.debug('tuTwoCurrentYear：{}'.format(tuTwoCurrentYear))
# logger.debug('tuTwoLastYear：{}'.format(tuTwoLastYear))
# logger.debug('tuTwobeforeLastYear：{}'.format(tuTwobeforeLastYear))
# jsPath = dirPath + '\\pyecharts_assets\\'
# fileImageTuTwoSave = dirPath + '\\[内部] 图2. 七日内供暖工单日诉求量.png'
# GenerateCharts.do_line_tu_image(tuOneLabels, tuTwoCurrentYear, '{}年'.format(timeCur.year), tuTwoLastYear,
#                                 '{}年'.format(timeCur.year - 1), tuTwobeforeLastYear, '{}年'.format(timeCur.year - 2),
#                                 fileImageTuTwoSave, jsPath, '图2. 七日内供暖工单日诉求量')




